<?php
class Payment{
	
	/**
	*	Esta función es responsable de 
	*	@autor: JSL
	*	@date: 31/01/2012
	*/
	function payment(){
		
	}
	
	/**
	*	Esta función es responsable de agregar un nuevo pago.
	*	@autor: JJR
	*	@date: 31/01/2012
	*/
	function addPayment($data){
		$sql = "INSERT INTO payment (pay_code, case_code, use_code, pay_value, pay_balance, pay_date_create, pay_comment) VALUES 
		  ( NULL, ".$data->case_code.", ".$data->use_code.", ".$data->pay_value.", ".$data->pay_balance.",'".date("Y-m-d H:i:s")."', '".$data->pay_comment."');"; 
		
		if ( mysql_query ($sql) )
			return mysql_insert_id();
		else
			return false;
	}
	
	/**
	*	Funcion encargada de obtener los detalles de un payment.
	*	@autor: JSL
	*	@date: 02/02/2012
	*/
	function getPayment($pay_code){
		$sql = "
		SELECT t1.pay_code, t1.case_code, t1.use_code, t1.pay_value, t1.pay_balance, t1.pay_date_create, t1.use_name, t1.use_lastname, t1.cli_code, t1.case_number, 
			t1.case_date_create, t1.case_balance, c.cli_name, c.cli_lastname FROM
		(SELECT t.pay_code, t.case_code, t.use_code, t.pay_value, t.pay_balance, t.pay_date_create, t.use_name, t.use_lastname, mc.cli_code, mc.case_number, mc.case_date_create,
		 mc.case_balance FROM
		(SELECT p.pay_code, p.case_code, p.use_code, p.pay_value, p.pay_balance, p.pay_date_create, u.use_name, u.use_lastname FROM payment p 
			JOIN user u ON p.use_code=u.use_code WHERE p.pay_code=".$pay_code.") t
		JOIN my_case mc ON t.case_code=mc.case_code) t1
		JOIN client c ON t1.cli_code= c.cli_code;";
		
		if ( $result = mysql_query ( $sql ) ){
			if ( $row = mysql_fetch_array ( $result ) ){
				$data->pay_code = $row["pay_code"];
				$data->case_code = $row["case_code"];
				$data->use_code = $row["use_code"];
				$data->pay_value = $row["pay_value"];
				$data->pay_balance = $row["pay_balance"];
				$data->pay_date_create = $row["pay_date_create"];
				$data->use_name = $row["use_name"];
				$data->use_lastname = $row["use_lastname"];
				$data->cli_code = $row["cli_code"];
				$data->case_number = $row["case_number"];
				$data->case_date_create = $row["case_date_create"];
				$data->case_balance = $row["case_balance"];
				$data->cli_name = $row["cli_name"];
				$data->cli_lastname = $row["cli_lastname"];
				
				return $data;
			}
			else
				return false;
		}
		else
			return false;
	}
	/**
	*	Esta función es responsable de listar los Pagos.
	*	@autor: JJR
	*	@date: 31/01/2012
	*/
	function listPayment($search = '', $order_by = '', $init = 0, $amount = 0, $cli_code = 0){
		$where = '';
		
		if ( $search != '' )
			$where = " WHERE (cli_name like '%".$search."%' OR cli_lastname like '%".$search."%' OR use_name like '%".$search."%' OR use_lastname like '%".$search."%' OR case_number like '%".$search."%' OR pay_date_create like '%".$search."%') ";
				
		if ($order_by == '')
			$order_by = ' ORDER BY pay_date_create ';
		
		$sql = "SELECT t5.case_code, t5.cli_code,t5.use_code, t5.case_number, t5.case_balance, t5.cli_name, t5.cli_lastname, t5.bus_name, t5.pay_value, t5.pay_balance, t5.pay_comment, t5.pay_date_create,u.use_name, u.use_lastname FROM
		(SELECT t.case_code, t.cli_code,t.use_code, t.case_number, t.case_balance, t.cli_name, t.cli_lastname, t.bus_name, t1.pay_value, t1.pay_balance, t1.pay_comment, t1.pay_date_create
		FROM
		(SELECT t0.case_code, t0.cli_code,t0.use_code, t0.case_number, t0.case_balance, t0.cli_name, t0.cli_lastname, bt.bus_name FROM
		(SELECT mc.case_code, mc.cli_code,mc.use_code, mc.bus_code, mc.case_number, mc.case_balance, c.cli_name, c.cli_lastname FROM my_case mc 
			JOIN client c ON mc.cli_code=c.cli_code WHERE c.cli_status <> 'Deleted') t0
		JOIN business_type bt ON t0.bus_code=bt.bus_code) t
		LEFT JOIN
		(SELECT p.pay_code, p.case_code, p.pay_value, p.pay_balance, p.pay_comment, p.pay_date_create FROM payment p
		JOIN (SELECT MAX(p.pay_code) AS pay_code FROM payment p GROUP BY p.case_code) t
		ON p.pay_code = t.pay_code) t1
		ON t.case_code=t1.case_code)t5 LEFT JOIN user u ON t5.use_code=u.use_code ".$where." ".$order_by;
		
		
		if ( ! ( $init == 0 && $amount == 0) )
			$sql .= " LIMIT ".$init.", ".$amount.";";
		
		if ( $result = mysql_query ($sql) ){
			$i =  0;
			while ( $row = mysql_fetch_array ($result) ){				
				
				$data[$i]->pay_code = $row["pay_code"];
				$data[$i]->case_code = $row["case_code"];
				$data[$i]->pay_value = $row["pay_value"];
				$data[$i]->pay_balance = $row["pay_balance"];
				$data[$i]->pay_date_create = $row["pay_date_create"];
				$data[$i]->pay_comment = $row["pay_comment"];
				$data[$i]->case_number = $row["case_number"];
				$data[$i]->cli_code = $row["cli_code"];
				$data[$i]->use_code = $row["use_code"];
				$data[$i]->cli_name = $row["cli_name"];
				$data[$i]->cli_lastname = $row["cli_lastname"];
				$data[$i]->use_name = $row["use_name"];
				$data[$i]->use_lastname = $row["use_lastname"];
				$data[$i]->case_balance = $row["case_balance"];
				$data[$i]->bus_name = $row["bus_name"];
				
				$i++;
			}
			
			return $data;
		}
		else
			return false;
	}
	
	/**
	*	Esta función es responsable de contar cuantos casos existen.
	*	@autor: JSL
	*	@date: 31/01/2012
	*/
	function countPayment($search, $cli_code = 0){
		$where = '';
		
		if ( $search != '' )
			$where = " WHERE (cli_name like '%".$search."%' OR cli_lastname like '%".$search."%' OR use_name like '%".$search."%' OR use_lastname like '%".$search."%' OR case_number like '%".$search."%' OR pay_date_create like '%".$search."%') ";
				
		if ($order_by == '')
			$order_by = ' ORDER BY pay_date_create ';
				
		$sql = "
		SELECT COUNT(*) AS cant
		FROM
		(SELECT t.case_code, t.cli_code,t.use_code, t.case_number, t.case_balance, t.cli_name, t.cli_lastname, t.bus_name, t1.pay_value, t1.pay_balance, t1.pay_comment, t1.pay_date_create
		FROM
		(SELECT t0.case_code, t0.cli_code,t0.use_code, t0.case_number, t0.case_balance, t0.cli_name, t0.cli_lastname, bt.bus_name FROM
		(SELECT mc.case_code, mc.cli_code,mc.use_code, mc.bus_code, mc.case_number, mc.case_balance, c.cli_name, c.cli_lastname FROM my_case mc 
			JOIN client c ON mc.cli_code=c.cli_code WHERE c.cli_status <> 'Deleted') t0
		JOIN business_type bt ON t0.bus_code=bt.bus_code) t
		LEFT JOIN
		(SELECT p.pay_code, p.case_code, p.pay_value, p.pay_balance, p.pay_comment, p.pay_date_create FROM payment p
		JOIN (SELECT MAX(p.pay_code) AS pay_code FROM payment p GROUP BY p.case_code) t
		ON p.pay_code = t.pay_code) t1
		ON t.case_code=t1.case_code)t5 LEFT JOIN user u ON t5.use_code=u.use_code ".$where." ".$order_by;
		
		
		if ( $result = mysql_query ($sql) ){
			if ( $row = mysql_fetch_array ($result) )
				return $row["cant"];
			else
				return false;		
		}		
		else
			return false;
	}	
	
	/**
	*	Esta función es responsable de listar los Pagos por cliente.
	*	@autor: JJR
	*	@date: 01/02/2012
	*/
	function listPaymentDetail($case_code){
						
		if ($order_by == '')
			$order_by = ' ORDER BY pay_date_create DESC ';
		
		$sql = "SELECT t2.pay_code, t2.case_code, t2.pay_value, t2.pay_balance, t2.pay_comment, t2.pay_date_create,t2.use_code,u.use_name,u.use_lastname FROM
		(SELECT t1.pay_code, t1.case_code, t1.pay_value, t1.pay_balance, t1.pay_comment, t1.pay_date_create,t1.use_code FROM
		(SELECT p.pay_code, p.case_code, p.pay_value, p.pay_balance, p.pay_comment, p.pay_date_create,p.use_code FROM payment p WHERE p.case_code=".$case_code.")t1
		LEFT JOIN my_case mc ON mc.case_code=t1.case_code)t2 LEFT JOIN user u ON t2.use_code=u.use_code".$order_by.""; 
		
		if ( $result = mysql_query ($sql) ){
			$i =  0;
			while ( $row = mysql_fetch_array ($result) ){				
				
				$data[$i]->pay_code = $row["pay_code"];
				$data[$i]->case_code = $row["case_code"];
				$data[$i]->pay_value = $row["pay_value"];
				$data[$i]->pay_balance = $row["pay_balance"];
				$data[$i]->pay_date_create = $row["pay_date_create"];
				$data[$i]->pay_comment = $row["pay_comment"];
				$data[$i]->use_code = $row["use_code"];
				$data[$i]->use_name = $row["use_name"];
				$data[$i]->use_lastname = $row["use_lastname"];
				
				$i++;
			}
			
			return $data;
		}
		else
			return false;
	}	
	
	/**
	*	Esta función es responsable de contar cuantos pagos existen por numero de caso.
	*	@autor: JJR
	*	@date: 01/02/2012
	*/
	function countPaymentDetail($case_code){
		$sql = "
		SELECT COUNT(*) AS cant FROM (
		SELECT p.pay_code, p.case_code, p.pay_value, p.pay_balance, p.pay_comment, p.pay_date_create,p.use_code FROM payment p WHERE p.case_code=".$case_code.")t1
		LEFT JOIN my_case mc ON mc.case_code=t1.case_code";
		
		if ( $result = mysql_query ($sql) ){
			if ( $row = mysql_fetch_array ($result) )
				return $row["cant"];
			else
				return false;		
		}		
		else
			return false;
	}
	
	/**
	*	Esta función es responsable de listar los tipos de negocio.
	*	@autor: JSL
	*	@date: 31/01/2012
	*/
	function listBusinessType(){
		$sql = "SELECT bt.bus_code, bt.bus_name FROM business_type bt;";
				
		if ( $result = mysql_query ($sql) ){
			$data = array ();
			$i =  0;
			while ( $row = mysql_fetch_array ($result) ){				
				$data[$i]->bus_code = $row["bus_code"];
				$data[$i]->bus_name = $row["bus_name"];
				
				$i++;
			}			
			return $data;
		}
		else
			return false;
	}
	
	/**
	*	Esta función es responsable de listar los clientes.
	*	@autor: JSL
	*	@date: 31/01/2012
	*/
	function listClients(){
		$sql = "SELECT cl.cli_code, cl.cli_name, cl.cli_lastname FROM client cl WHERE cl.cli_status='Active';";
				
		if ( $result = mysql_query ($sql) ){
			$i =  0;
			while ( $row = mysql_fetch_array ($result) ){
				$data[$i]->cli_code = $row["cli_code"];
				$data[$i]->cli_name = $row["cli_name"];
				$data[$i]->cli_lastname = $row["cli_lastname"];
				
				$i++;
			}			
			return $data;
		}
		else
			return false;
	}
	
	/**
	*	Esta función obtiene los numeros de caso de un cliente.
	*	@autor: JSL
	*	@date: 31/01/2012
	*/
	function listCasesClient($cli_code = 0){		
		$sql = "		
		SELECT cs.case_code, cs.case_number, bt.bus_name FROM business_type bt JOIN my_case cs ON bt.bus_code=cs.bus_code WHERE cs.cli_code=".$cli_code;
		
		if ( $result = mysql_query ($sql) ){
			$i =  0;
			while ( $row = mysql_fetch_array ($result) ){				
				
				$data[$i]->case_code = $row["case_code"];
				$data[$i]->case_number = $row["case_number"];
				$data[$i]->bus_name = $row["bus_name"];
				
				$i++;
			}
			
			return $data;
		}
		else
			return false;
	}
	
	/**
	*	Esta funcion obtiene el saldo que tiene un caso.
	*	@autor: JSL
	*	@date: 01/02/2012
	*/
	function getPayBalanceDue($case_code, $pay_code = 0){
		$where = '';
		if ( $pay_code > 0 )
			$where = " AND p.pay_code NOT IN(".$pay_code.")";
					
		$sql = "SELECT mc.case_balance-(SELECT IFNULL(SUM(p.pay_value), 0) AS sum_payment FROM payment p WHERE p.case_code=".$case_code." ".$where.") AS pay_balance_due 
		FROM my_case mc WHERE mc.case_code=".$case_code;
		
		//die($sql);
		if ( $result = mysql_query($sql) ){
			if ( $row = mysql_fetch_array ( $result ) )
				return $row["pay_balance_due"];
			else
				return false;
		}
		else
			return false;
	}
	
	/**
	*	Esta función es responsable de listar las deudas de los clientes.
	*	@autor: JJR
	*	@date: 02/09/2012
	*/
	function listPaymentDebts($use_code){
		
		$sql = "SELECT t.case_code, t.cli_code,t.use_code, t.case_number, t.case_balance, t.cli_name, t.cli_lastname, t.bus_name, t1.pay_value, t1.pay_balance, t1.pay_comment, t1.pay_date_create FROM (SELECT t0.case_code, t0.cli_code,t0.use_code, t0.case_number, t0.case_balance, t0.cli_name, t0.cli_lastname, bt.bus_name FROM (SELECT mc.case_code, mc.cli_code,mc.use_code, mc.bus_code, mc.case_number, mc.case_balance, c.cli_name, c.cli_lastname FROM my_case mc JOIN client c ON mc.cli_code=c.cli_code) t0 JOIN business_type bt ON t0.bus_code=bt.bus_code) t LEFT JOIN (SELECT p.pay_code, p.case_code, p.pay_value, p.pay_balance, p.pay_comment, p.pay_date_create FROM payment p JOIN (SELECT MAX(p.pay_code) AS pay_code FROM payment p GROUP BY p.case_code) t ON p.pay_code = t.pay_code) t1 ON t.case_code=t1.case_code WHERE use_code=".$use_code."  ORDER BY pay_date_create DESC  ";
		
		
		if ( $result = mysql_query ($sql) ){
			$i =  0;
			while ( $row = mysql_fetch_array ($result) ){				
				
				$data[$i]->pay_code = $row["pay_code"];
				$data[$i]->case_code = $row["case_code"];
				$data[$i]->pay_value = $row["pay_value"];
				$data[$i]->pay_balance = $row["pay_balance"];
				$data[$i]->pay_date_create = $row["pay_date_create"];
				$data[$i]->pay_comment = $row["pay_comment"];
				$data[$i]->case_number = $row["case_number"];
				$data[$i]->cli_code = $row["cli_code"];
				$data[$i]->use_code = $row["use_code"];
				$data[$i]->cli_name = $row["cli_name"];
				$data[$i]->cli_lastname = $row["cli_lastname"];
				$data[$i]->use_name = $row["use_name"];
				$data[$i]->use_lastname = $row["use_lastname"];
				$data[$i]->case_balance = $row["case_balance"];
				$data[$i]->bus_name = $row["bus_name"];
				
				$i++;
			}
			
			return $data;
		}
		else
			return false;
	}	
}
?>